﻿using OestsCommon.Model;
using System;
using System.Collections.Generic;
using System.Data;

namespace OestsDataBase
{
    /// <summary>
    /// 对应项目岗位关系表
    /// </summary>
    public static class ProjectPostDAL
    {
        //岗位
        public static void CreateTable()
        {
            string sqlCheck = "if object_id( 'T_Project_Post') is not null select 1 else select 0";
            if ((int)SqlHelper.ExecuteScalar(sqlCheck) == 0)
            {
                string sqlCreacte = @"CREATE TABLE T_Project_Post (project_id bigint not null ,post_id bigint not null, PRIMARY KEY (project_id,post_id))";

                SqlHelper.ExecuteNonQuery(sqlCreacte);
            }
        }

        public static List<ProjectPostModel> GetData()
        {
            List<ProjectPostModel> list = new List<ProjectPostModel>();
            string strSql = "select * from T_Project_Post";
            DataTable dt = SqlHelper.ExecuteDataTable(strSql);
            if (dt == null) { return null; }
            foreach (DataRow dr in dt.Rows)
            {
                ProjectPostModel model = new ProjectPostModel();
                model.PostId = Int32.Parse(dr["post_id"].ToString());
                model.ProjectId = Int32.Parse(dr["project_id"].ToString());
                list.Add(model);
            }
            return list;
        }

        public static List<PostModel> GetPostsInProject(int projectID)
        {
            //获取指定项目下的岗位ID
            List<int> listPostID = new List<int>();
            string strSql = "select post_id from T_Project_Post where project_id=" + projectID;
            DataTable dt = SqlHelper.ExecuteDataTable(strSql);
            foreach (DataRow dr in dt.Rows)
            {
                int postId = Int32.Parse(dr["post_id"].ToString());
                listPostID.Add(postId);
            }
            //在岗位表中获取
            List<PostModel> list = new List<PostModel>();
            foreach (var item in listPostID)
            {
                PostModel model = PostDAL.GetPostModel(item);
                if (model != null)
                    list.Add(model);
            }
            return list;

        }
    }
}
